from datetime import datetime

from sqlalchemy import Column, Integer, String, JSON, TEXT, func
from sqlalchemy.orm import declarative_base, sessionmaker

from database.mysql_utils import MysqlUtils

Base = declarative_base()


class Asin(Base):
    __tablename__ = 'asin'

    # 定义表的列
    id = Column(Integer, primary_key=True)
    listing_id = Column(String(255))
    sid = Column(Integer)
    asin = Column(String(255))
    parent_asin = Column(String(255))
    store_manager = Column(String(255))
    operations = Column(String(255))
    date = Column(String(255))
    marketplace = Column(String(255))
    seller_sku = Column(String(255))
    fnsku = Column(String(255))
    small_image_url = Column(String(255))
    status = Column(Integer)
    is_delete = Column(Integer)
    item_name = Column(TEXT)
    local_sku = Column(String(255))
    local_name = Column(String(255))
    currency_code = Column(String(255))
    price = Column(String(255))
    landed_price = Column(String(255))
    listing_price = Column(String(255))
    shipping = Column(String(255))
    points = Column(String(255))
    quantity = Column(Integer)
    afn_fulfillable_quantity = Column(Integer)
    afn_unsellable_quantity = Column(Integer)
    reserved_fc_transfers = Column(Integer)
    reserved_fc_processing = Column(Integer)
    reserved_customerorders = Column(Integer)
    afn_inbound_shipped_quantity = Column(Integer)
    afn_inbound_working_quantity = Column(Integer)
    afn_inbound_receiving_quantity = Column(Integer)
    open_date = Column(String(255))
    open_date_display = Column(String(255))
    listing_update_date = Column(String(255))
    seller_rank = Column(Integer)
    seller_category = Column(String(255))
    review_num = Column(Integer)
    last_star = Column(String(255))
    fulfillment_channel_type = Column(String(255))
    principal_info = Column(JSON)
    seller_category_new = Column(JSON)
    pair_update_time = Column(String(255))
    first_order_time = Column(String(255))
    on_sale_time = Column(String(255))
    dimension_info = Column(JSON)
    small_rank = Column(JSON)
    global_tags = Column(JSON)


Base.metadata.create_all(MysqlUtils.mysql_connect())
Session = sessionmaker(bind=MysqlUtils.mysql_connect())


class AsinData:

    @staticmethod
    def insert_asin_info(asin_dict):
        session = Session()
        try:
            for one_asin_info in asin_dict:
                listing_id = one_asin_info.get("listing_id"),
                sid = one_asin_info.get("sid"),
                marketplace = one_asin_info.get("marketplace"),
                seller_sku = one_asin_info.get("seller_sku"),
                fnsku = one_asin_info.get("fnsku"),
                asin = one_asin_info.get("asin"),
                date = datetime.now().strftime("%y%m%d")
                parent_asin = one_asin_info.get("parent_asin"),
                small_image_url = one_asin_info.get("small_image_url"),
                status = one_asin_info.get("status"),
                is_delete = one_asin_info.get("is_delete"),
                item_name = one_asin_info.get("item_name"),
                local_sku = one_asin_info.get("local_sku"),
                local_name = one_asin_info.get("local_name"),
                currency_code = one_asin_info.get("currency_code"),
                price = one_asin_info.get("price"),
                landed_price = one_asin_info.get("landed_price"),
                listing_price = one_asin_info.get("listing_price"),
                shipping = one_asin_info.get("shipping"),
                points = one_asin_info.get("points"),
                quantity = one_asin_info.get("quantity"),
                afn_fulfillable_quantity = one_asin_info.get("afn_fulfillable_quantity"),
                afn_unsellable_quantity = one_asin_info.get("afn_unsellable_quantity"),
                reserved_fc_transfers = one_asin_info.get("reserved_fc_transfers"),
                reserved_fc_processing = one_asin_info.get("reserved_fc_processing"),
                reserved_customerorders = one_asin_info.get("reserved_customerorders"),
                afn_inbound_shipped_quantity = one_asin_info.get("afn_inbound_shipped_quantity"),
                afn_inbound_working_quantity = one_asin_info.get("afn_inbound_working_quantity"),
                afn_inbound_receiving_quantity = one_asin_info.get("afn_inbound_receiving_quantity"),
                open_date = one_asin_info.get("open_date"),
                open_date_display = one_asin_info.get("open_date_display"),
                listing_update_date = one_asin_info.get("listing_update_date"),
                seller_rank = one_asin_info.get("seller_rank"),
                seller_category = one_asin_info.get("seller_category"),
                review_num = one_asin_info.get("review_num"),
                last_star = one_asin_info.get("last_star"),
                fulfillment_channel_type = one_asin_info.get("fulfillment_channel_type"),
                principal_info = one_asin_info.get("principal_info"),
                seller_category_new = one_asin_info.get("seller_category_new"),
                pair_update_time = one_asin_info.get("pair_update_time"),
                first_order_time = one_asin_info.get("first_order_time"),
                on_sale_time = one_asin_info.get("on_sale_time"),
                dimension_info = one_asin_info.get("dimension_info"),
                small_rank = one_asin_info.get("small_rank"),
                global_tags = one_asin_info.get("global_tags")
                asin_obj = Asin(
                    listing_id=listing_id,
                    sid=sid,
                    marketplace=marketplace,
                    seller_sku=seller_sku,
                    fnsku=fnsku,
                    asin=asin,
                    date=date,
                    parent_asin=parent_asin,
                    small_image_url=small_image_url,
                    status=status,
                    is_delete=is_delete,
                    item_name=item_name,
                    local_sku=local_sku,
                    local_name=local_name,
                    currency_code=currency_code,
                    price=price,
                    landed_price=landed_price,
                    listing_price=listing_price,
                    shipping=shipping,
                    points=points,
                    quantity=quantity,
                    afn_fulfillable_quantity=afn_fulfillable_quantity,
                    afn_unsellable_quantity=afn_unsellable_quantity,
                    reserved_fc_transfers=reserved_fc_transfers,
                    reserved_fc_processing=reserved_fc_processing,
                    reserved_customerorders=reserved_customerorders,
                    afn_inbound_shipped_quantity=afn_inbound_shipped_quantity,
                    afn_inbound_working_quantity=afn_inbound_working_quantity,
                    afn_inbound_receiving_quantity=afn_inbound_receiving_quantity,
                    open_date=open_date,
                    open_date_display=open_date_display,
                    listing_update_date=listing_update_date,
                    seller_rank=seller_rank,
                    seller_category=seller_category,
                    review_num=review_num,
                    last_star=last_star,
                    fulfillment_channel_type=fulfillment_channel_type,
                    principal_info=principal_info,
                    seller_category_new=seller_category_new,
                    pair_update_time=pair_update_time,
                    first_order_time=first_order_time,
                    on_sale_time=on_sale_time,
                    dimension_info=dimension_info,
                    small_rank=small_rank,
                    global_tags=global_tags
                )
                session.add(asin_obj)
            session.commit()
        except Exception as e:
            print("写入每日asin信息失败")
        finally:
            session.close()

    @staticmethod
    def get_principal_info_by_sid(sid):
        session = Session()
        ret = session.query(Asin.principal_info).filter(Asin.sid == sid).first()
        if not ret:
            return ""
        principal_name = ret[0][0][0].get("principal_name")
        print(principal_name)
        return principal_name

    @staticmethod
    def get_asin_num_by_date():
        session = Session()
        ret = session.query(func.count(Asin.id)).filter(Asin.date == "250310").scalar()
        session.close()
        print(ret)
        return ret

    @staticmethod
    def get_asin_max_date():
        session = Session()
        max_date = session.query(func.max(Asin.date)).scalar()
        return max_date

    @staticmethod
    def del_asin_by_date(date):
        session = Session()
        session.query(Asin).filter(Asin.date == date).delete()
        session.commit()
        session.close()

if __name__ == '__main__':
    # AsinData.get_principal_info_by_sid("13008")
    # AsinData.get_asin_num_by_date()
    AsinData.del_asin_by_date("250310")
    # max_date = AsinData.get_asin_max_date()
    # print(max_date, print(type(max_date)))
    # today = datetime.today().strftime("%y%m%d")
    # print(today, type(today))
    # if max_date == today:
    #     print("ok")
